﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace AlphaSolutionsGP
{
    public class Grade
    {
        #region attributes
        //result ID
        private int resultId;
        //student id
        private string studentID;
        //assessment ID
        private int assId;
        //date submitted 
        private String dateSubmission;
        //qualification grade
        private String grade;
        #endregion


        #region constructors

        //default constructor
        public Grade()
        {
            resultId = 0;
            assId = 0;
            dateSubmission = "";
            grade = "";
        }
        //constructor with arguments
        public Grade(int RESULTID, string STUDENTID, int ASSID, string DATESUB, string GRADE)
        {
            this.resultId = RESULTID;
            this.studentID = STUDENTID;
            this.assId = ASSID;
            this.dateSubmission = DATESUB;
            this.grade = GRADE;
        }

        #endregion


        #region getters and setters
        public int ResultID
        {
            get { return resultId; }
            set { resultId = value; }
        }

        public int AssID
        {
            get { return assId; }
            set { assId = value; }
        }

        public String DateSubmission
        {
            get { return dateSubmission; }
            set { dateSubmission = value; }
        }

        public String AssGrade
        {
            get { return grade; }
            set { grade = value; }
        }

        public String SudentID
        {
            get { return studentID; }
            set { studentID = value; }
        }
        #endregion


        #region methods
        //change the old values to the new values amended into the form
        public void amendGrade(string resultID)
        {
            MySqlConnection connection = new MySqlConnection("server=194.81.104.22;" + "User Id=alpha;" + "database=alpha_db;" + "password=bravo;");
            try
            {
                connection.Open();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show("Connection failed");
            }
            try
            {
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "UPDATE RESULT SET ASSESSMENT_assessment_ID = '"+ AssID +"',  date_submission='" + convertToMySQLDate(DateSubmission)+ "', grade='" + AssGrade + "' WHERE result_ID=" + resultID;
                MySqlDataReader run = cmd.ExecuteReader();
                System.Windows.Forms.MessageBox.Show("The grade has been modified.", "Modify OK", System.Windows.Forms.MessageBoxButtons.OK);
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("Unable to modify due to the following error:\n\n" + ex.ToString());
            }
        }

        // create a new qualification
        public void createGrade(string sId)
        {
            MySqlConnection connection = new MySqlConnection("server=194.81.104.22;" + "User Id=alpha;" + "database=alpha_db;" + "password=bravo;");
            try
            {
                connection.Open();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show("Connection failed");
            }
            try
            {                
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "INSERT INTO RESULT (STUDENT_student_ID, ASSESSMENT_assessment_ID, date_submission, grade) VALUES ('" + sId  + "','" + Convert.ToInt16(AssID) + "','" + convertToMySQLDate(this.dateSubmission) + "','" + AssGrade + "');";
                MySqlDataReader run = cmd.ExecuteReader();
                System.Windows.Forms.MessageBox.Show("The Grade has been inserted.", "Insert OK", System.Windows.Forms.MessageBoxButtons.OK);
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("Unable to insert due to the following error:\n\n" + ex.ToString());
            }
        }

        // remove an existing qualification from the DB
        public void removeGrade(string rID)
        {
            MySqlConnection connection = new MySqlConnection("server=194.81.104.22;" + "User Id=alpha;" + "database=alpha_db;" + "password=bravo;");
            try
            {
                connection.Open();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show("Connection failed");
            }
            try
            {
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "DELETE FROM RESULT WHERE result_ID =" + rID;
                MySqlDataReader run = cmd.ExecuteReader();
                System.Windows.Forms.MessageBox.Show("The Grade has been deleted.", "Delete OK", System.Windows.Forms.MessageBoxButtons.OK);
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("Unable to delete due to the following error:\n\n" + ex.ToString());
            }
        }

        //method to copy data from one grade to another
        public void copyGrade(Grade copy)
        {
            this.resultId = copy.resultId;
            this.assId = copy.assId;
            this.dateSubmission = copy.dateSubmission;
            this.grade = copy.grade;
        }

        //this method takes a string date as argument and convert it to mysql format date
        //used to convert the date when it comes to do INSERT into tables
        public static string convertToMySQLDate(string date)
        {
            //string dateToString = Convert.ToString(date);
            DateTime toDate = DateTime.Parse(date);
            string finalDate = toDate.Date.ToString("yyyy/MM/dd");

            return finalDate;
        }
        #endregion

    }
}